package com.b511.swpu.cementbackend;

import com.b511.swpu.cementbackend.entity.*;
import com.b511.swpu.cementbackend.repository.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ImportDataCementWork {

    @Autowired
    CM_WorkBasicRepository workBasicRepository;

    @Autowired
    BA_WellRepository baWellRepository;

    @Autowired
    CM_WK_MudPropertyRepository mudPropertyRepository;

    @Autowired
    CM_WK_MudFormulaRepository mudFormulaRepository;

    @Autowired
    CM_WK_WorkRecordRepository workRecordRepository;

    // 附件消耗
    @Autowired
    CM_WK_AccessoryConsumeRepository cmWkAccessoryConsumeRepository;

    // 添加剂消耗
    @Autowired
    CM_WK_AdditiveConsumeRepository cmWkAdditiveConsumeRepository;

    // 从老数据库导入到 CM_WK_BASIC excel导入
    // 固井作业基本信息
    // 12.3 成功导入
    @Test
    public void initWkBasicData(){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MM-yyyy");//注意月份是MM

        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:固井作业基本数据导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("固井基本信息");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_BASIC> basicList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // 固井方式 1
                    // 套管尺寸 2
                    // 施工日期 3
                    // 报告日期 4
                    // 井眼尺寸 5
                    // 井深 6
                    // 套管下深 7
                    // 填表人/报告人/固井工程师 8
                    // 平台类型 9
                    CM_WK_BASIC tmpBasic = new CM_WK_BASIC();

                    // 查询当前井名是否存在于 BA_WELL 中
                    String wellName;
                    wellName = row.getCell(0).toString();

                    BA_Well tmpWell = baWellRepository.findByWellName(wellName);
                    if (null != tmpWell){
                        if(null != row.getCell(0)){
                            tmpBasic.setWellName(row.getCell(0).toString());
                        }
                        if(null != row.getCell(1)){
                            // 判断固井方式
                            // 历史数据默认设定为 自升式-套管估计-单级单封
                            tmpBasic.setCementType("单级");
                            tmpBasic.setReportType("自升式-套管固井-单级单封");

                        }
                        // caseSize 套管尺寸
                        if(null != row.getCell(2)){
                            tmpBasic.setCaseSize(row.getCell(2).toString());
                        }
                        if(null != row.getCell(3)){
                            System.out.println(row.getCell(3).toString());
                            tmpBasic.setWorkDate(row.getCell(3).getDateCellValue());
                        }
                        if(null != row.getCell(4)){
                            tmpBasic.setReportDate(row.getCell(4).getDateCellValue());
                        }
                        // HoleSize 井眼尺寸
                        if(null != row.getCell(5)){
                            tmpBasic.setHoleSize(row.getCell(5).toString());
                        }
                        if(null != row.getCell(6)){
                            tmpBasic.setHoleDepth(Double.valueOf(row.getCell(6).toString()));
                        }
                        if(null != row.getCell(7)){
                            tmpBasic.setCaseDepth(Double.valueOf(row.getCell(7).toString()));
                        }
                        if(null != row.getCell(8)){
                            tmpBasic.setCementEngineer(row.getCell(8).toString());
                        }
                        if(null != row.getCell(9)){
                            tmpBasic.setPlatformType(row.getCell(9).toString());
                        } else {
                            tmpBasic.setPlatformType("暂无平台类型!");
                        }
                        tmpBasic.setWorkCategory("作业类别缺失!");

                        basicList.add(tmpBasic);
                    }
                }
            }
            if(0 < basicList.size()){
                workBasicRepository.saveAll(basicList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 流体性能 CM_WK_MUD_PROPERTY
    @Test
    public void initMudPropertyData(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:流体性能导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("Sheet1");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_MudProperty> mudPropertyList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // ExperimentItem 1
                    // 套管尺寸 2
                    // 密度 3
                    // 造浆率 4
                    // api失水 5
                    // api自由水 6
                    // 100稠化时间 7
                    // 24hr 抗压强度 8
                    CM_WK_MudProperty tmpMudProperty = new CM_WK_MudProperty();

                    // Item_No = 1
                    tmpMudProperty.setItemNo(1);

                    if(null != row.getCell(0)){
                        tmpMudProperty.setWellName(row.getCell(0).toString());
                    }
                    if(null != row.getCell(1)){
                        tmpMudProperty.setExprementItem(row.getCell(1).toString());
                    }
                    if(null != row.getCell(2)){
                        System.out.println(row.getCell(2).toString());
                        tmpMudProperty.setCasingSize(row.getCell(2).toString());
                    }
                    if(null != row.getCell(3)){
                        tmpMudProperty.setMudDensity(row.getCell(3).toString());
                    }
                    if(null != row.getCell(4)){
                        tmpMudProperty.setMudYield(Double.valueOf(row.getCell(4).toString()));
                    }
                    if(null != row.getCell(5)){
                        tmpMudProperty.setApiFluidLoss(Double.valueOf(row.getCell(5).toString()));
                    }
                    if(null != row.getCell(6)){
                        tmpMudProperty.setFreeWater(Double.valueOf(row.getCell(6).toString()));
                    }
                    if(null != row.getCell(8)){
                        tmpMudProperty.setPressure24(Double.valueOf(row.getCell(8).toString()));
                    }

                    mudPropertyList.add(tmpMudProperty);
                }
            }
            if(0 < mudPropertyList.size()){
                mudPropertyRepository.saveAll(mudPropertyList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 水泥浆配方 CM_WK_MUD_FORMULA
    @Test
    public void initFormulaData(){

        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:固井作业基本数据导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("水泥浆配方");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_MudFormula> mudFormulaList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // ExperimentItem 1
                    // 套管尺寸 2
                    // 材料名称 3
                    // 材料类别 4
                    // 密度 5
                    // 加量比例 6
                    CM_WK_MudFormula tmpMudFormula = new CM_WK_MudFormula();

                    // 默认 ItemNo=1
                    tmpMudFormula.setItemNo(1);

                    if(null != row.getCell(0)){
                        tmpMudFormula.setWellName(row.getCell(0).toString());
                    }
                    if(null != row.getCell(1)){
                        tmpMudFormula.setExprementItem(row.getCell(1).toString());
                    }
                    if(null != row.getCell(2)){
                        tmpMudFormula.setCasingSize(row.getCell(2).toString());
                    }
                    if(null != row.getCell(3)){
                        tmpMudFormula.setMaterialName(row.getCell(3).toString());
                    }
                    if(null != row.getCell(4)){
                        tmpMudFormula.setMaterialCategory(row.getCell(4).toString());
                    }
                    if(null != row.getCell(6)){
                        tmpMudFormula.setMaterialDosage(row.getCell(6).toString());
                    }
                    mudFormulaList.add(tmpMudFormula);
                }
            }
            if(0 < mudFormulaList.size()){
                mudFormulaRepository.saveAll(mudFormulaList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 施工记录
    @Test
    public void initWorkData(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:固井作业基本数据导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("施工记录");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_WorkRecord> workRecordList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // 流体名称 1
                    // 实验项目 2
                    // 套管尺寸 3
                    // 开始时间 4
                    // 结束时间 5
                    // 施工量 6 vol
                    // 最小排量 7
                    // 最大排量 8
                    // 最小密度 9
                    // 最大密度 10
                    // 最小压力 11
                    // 最大压力 12
                    CM_WK_WorkRecord workRecord = new CM_WK_WorkRecord();

                    // TODO: 未考虑 WorkNo

                    if(null != row.getCell(0)){
                        workRecord.setWellName(row.getCell(0).toString());
                    }
                    if(null != row.getCell(2)){
                        workRecord.setWorkItem(row.getCell(2).toString());
                    }
                    if(null != row.getCell(3)){
                        workRecord.setCasingSize(row.getCell(3).toString());
                    }
                    if(null != row.getCell(4)){
                        workRecord.setStartTime(row.getCell(4).toString());
                    }
                    if(null != row.getCell(5)){
                        workRecord.setEndTime(row.getCell(5).toString());
                    }
                    if(null != row.getCell(6)){
                        workRecord.setVol(row.getCell(6).toString());
                    }
                    if(null != row.getCell(7)){
                        workRecord.setDispMin(row.getCell(7).toString());
                    }
                    if(null != row.getCell(8)){
                        workRecord.setDispMax(row.getCell(8).toString());
                    }
                    if(null != row.getCell(9)){
                        workRecord.setFluidDensity(row.getCell(9).toString());
                    }
                    if(null != row.getCell(11)){
                        workRecord.setPumpMin(row.getCell(11).toString());
                    }
                    if(null != row.getCell(12)){
                        workRecord.setPumpMax(row.getCell(12).toString());
                    }

                    workRecordList.add(workRecord);
                }
            }
            if(0 < workRecordList.size()){
                workRecordRepository.saveAll(workRecordList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 作业添加剂消耗
    @Test
    public void initAddMaterial(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:固井作业基本数据导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("作业添加剂消耗");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_AdditiveConsume> workRecordList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // 套管尺寸 1
                    // 添加剂 2
                    // 用量 3
                    // 单位 4
                    CM_WK_AdditiveConsume additiveConsume = new CM_WK_AdditiveConsume();

                    // TODO: 未考虑 UnitPrice Money

                    if(null != row.getCell(0)){
                        additiveConsume.setWellName(row.getCell(0).toString());
                    }
                    if(null != row.getCell(1)){
                        additiveConsume.setCasingSize(row.getCell(1).toString());
                    }
                    if(null != row.getCell(2)){
                        additiveConsume.setAdditiveName(row.getCell(2).toString());
                    }
                    if(null != row.getCell(3)){
                        additiveConsume.setAmount(Double.valueOf(row.getCell(3).toString()));
                    }
                    if(null != row.getCell(4)){
                        additiveConsume.setUnit(row.getCell(4).toString());
                    }

                    workRecordList.add(additiveConsume);
                }
            }
            if(0 < workRecordList.size()){
                cmWkAdditiveConsumeRepository.saveAll(workRecordList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 作业附件消耗
    @Test
    public void initAddConsump(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:固井作业基本数据导入.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("作业附件消耗");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<CM_WK_AccessoryConsume> accessoryConsumeList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 井名 0
                    // 套管尺寸 1
                    // 附件名称 2
                    // 规格型号 3
                    // 单位 4
                    // 用量 5
                    CM_WK_AccessoryConsume additiveConsume = new CM_WK_AccessoryConsume();

                    // TODO: 未考虑 UnitPrice Money

                    if(null != row.getCell(0)){
                        additiveConsume.setWellName(row.getCell(0).toString());
                    }
                    if(null != row.getCell(1)){
                        additiveConsume.setCasingSize(row.getCell(1).toString());
                    }
                    if(null != row.getCell(2)){
                        additiveConsume.setAccessoryName(row.getCell(2).toString());
                    }
                    if(null != row.getCell(4)){
                        additiveConsume.setUnit(row.getCell(4).toString());
                    }
                    if(null != row.getCell(5)){
                        additiveConsume.setAmount(Double.valueOf(row.getCell(5).toString()));
                    }
                    accessoryConsumeList.add(additiveConsume);
                }
            }
            if(0 < accessoryConsumeList.size()){
                cmWkAccessoryConsumeRepository.saveAll(accessoryConsumeList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
